package com.tataben.db.util;

import com.tataben.db.converter.ColumnConverter;
import com.tataben.db.converter.ColumnType;
import com.tataben.db.converter.DLCCFactory;
import com.tataben.db.selector.DLWhereBuilder;
import com.tataben.db.table.DLColumn;
import com.tataben.db.table.DLTable;
import com.tencent.wcdb.database.SQLiteDatabase;
import com.tencent.wcdb.database.SQLiteStatement;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;

/**
 * 基础SQL语句生成
 * Created by dlilang on 2017/9/1 0001.
 */
public class DLSQLUtils {
    private DLSQLUtils(){}
    // TODO Insert
    public static SQLiteStatement buildInsertSqlInfo(DLTable<?> table, Object entity, SQLiteDatabase db) throws Exception {
        List<MMJKeyValue> keyValueList = entity2KeyValueList(table, entity);
        if (keyValueList.size() == 0) return null;
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO ");
        builder.append("\"").append(table.getName()).append("\"");
        builder.append(" (");
        for (MMJKeyValue kv : keyValueList) {
            builder.append("\"").append(kv.key).append("\"").append(',');
        }
        builder.deleteCharAt(builder.length() - 1);
        builder.append(") VALUES (");
        int length = keyValueList.size();
        for (int i = 0; i < length; i++) {
            builder.append("?,");
        }
        builder.deleteCharAt(builder.length() - 1);
        builder.append(")");
        return statement(db, builder.toString(), keyValueList);
    }
    // TODO Delete
    public static String buildDeleteSqlInfo(DLTable<?> table, Object entity) throws Exception {
        DLColumn id = table.getId();
        Object idValue = id.getColumnValue(entity);
        if (idValue == null) {
            throw new Exception("this entity[" + table.getEntityType() + "]'s id value is null");
        }
        StringBuilder builder = new StringBuilder("DELETE FROM ");
        builder.append("\"").append(table.getName()).append("\"");
        builder.append(" WHERE ").append(DLWhereBuilder.b(id.getName(), "=", idValue));
        return builder.toString();
    }
    public static String buildDeleteSqlInfoById(DLTable<?> table, Object idValue) throws Exception {
        DLColumn id = table.getId();
        if (idValue == null) {
            throw new Exception("this entity[" + table.getEntityType() + "]'s id value is null");
        }
        StringBuilder builder = new StringBuilder("DELETE FROM ");
        builder.append("\"").append(table.getName()).append("\"");
        builder.append(" WHERE ").append(DLWhereBuilder.b(id.getName(), "=", idValue));
        return builder.toString();
    }
    public static String buildClearSqlInfo(DLTable<?> table){
        StringBuilder builder = new StringBuilder("DELETE FROM ");
        builder.append("\"").append(table.getName()).append("\"");
        return builder.toString();
    }

    // TODO Update
    public static SQLiteStatement buildUpdateSqlInfo(DLTable<?> table, Object entity, SQLiteDatabase db, String... updateColumnNames) throws Exception {
        List<MMJKeyValue> keyValueList = entity2KeyValueList(table, entity);
        if (keyValueList.size() == 0) return null;

        HashSet<String> updateColumnNameSet = null;
        if (updateColumnNames != null && updateColumnNames.length > 0) {
            updateColumnNameSet = new HashSet<>(updateColumnNames.length);
            Collections.addAll(updateColumnNameSet, updateColumnNames);
        }

        DLColumn id = table.getId();
        Object idValue = id.getColumnValue(entity);

        if (idValue == null) {
            throw new Exception("this entity[" + table.getEntityType() + "]'s id value is null");
        }

        StringBuilder builder = new StringBuilder("UPDATE ");
        builder.append("\"").append(table.getName()).append("\"");
        builder.append(" SET ");
        for (MMJKeyValue kv : keyValueList) {
            if (updateColumnNameSet == null || updateColumnNameSet.contains(kv.key)) {
                builder.append("\"").append(kv.key).append("\"").append("=?,");
            }
        }
        builder.deleteCharAt(builder.length() - 1);
        builder.append(" WHERE ").append(DLWhereBuilder.b(id.getName(), "=", idValue));
        return statement(db, builder.toString(), keyValueList);
    }

    // TODO CREATE TABLE
    public static String buildCreateTableSqlInfo(DLTable<?> table) throws Exception {
        DLColumn id = table.getId();

        StringBuilder builder = new StringBuilder();
        builder.append("CREATE TABLE IF NOT EXISTS ");
        builder.append("\"").append(table.getName()).append("\"");
        builder.append(" ( ");

        if (id.isAutoId()) {
            builder.append("\"").append(id.getName()).append("\"").append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
        } else {
            builder.append("\"").append(id.getName()).append("\"").append(id.getColumnDbType()).append(" PRIMARY KEY, ");
        }

        Collection<DLColumn> columns = table.getColumnMap().values();
        for (DLColumn column : columns) {
            if (column.isId()) continue;
            builder.append("\"").append(column.getName()).append("\"");
            builder.append(' ').append(column.getColumnDbType());
            builder.append(' ').append(column.getProperty());
            builder.append(',');
        }

        builder.deleteCharAt(builder.length() - 1);
        builder.append(" )");
        return builder.toString();
    }
    // TODO last Auto ID
    public static String buildLastId(DLTable<?> table){
        return "SELECT seq FROM sqlite_sequence WHERE name='" + table.getName() + "' LIMIT 1";
    }

    // TODO 辅助
    private static SQLiteStatement statement(SQLiteDatabase db, String sql, List<MMJKeyValue> kvs){
        SQLiteStatement result = db.compileStatement(sql);
        for (MMJKeyValue kv : kvs) {
            int i = kvs.indexOf(kv) + 1;
            Object value = DLColumnUtils.convert2DbValueIfNeeded(kv.value);
            if (value == null) {
                result.bindNull(i);
            } else {
                ColumnConverter converter = DLCCFactory.getColumnConverter(value.getClass());
                ColumnType type = converter.getColumnDbType();
                switch (type) {
                    case INTEGER:
                        result.bindLong(i, ((Number) value).longValue());
                        break;
                    case REAL:
                        result.bindDouble(i, ((Number) value).doubleValue());
                        break;
                    case TEXT:
                        result.bindString(i, value.toString());
                        break;
                    case BLOB:
                        result.bindBlob(i, (byte[]) value);
                        break;
                    default:
                        result.bindNull(i);
                        break;
                }
            }
        }
        return result;
    }
    private static List<MMJKeyValue> entity2KeyValueList(DLTable<?> table, Object entity) {
        Collection<DLColumn> columns = table.getColumnMap().values();
        List<MMJKeyValue> keyValueList = new ArrayList<>(columns.size());
        for (DLColumn column : columns) {
            MMJKeyValue kv = column2KeyValue(entity, column);
            if (kv != null) {
                keyValueList.add(kv);
            }
        }

        return keyValueList;
    }
    private static MMJKeyValue column2KeyValue(Object entity, DLColumn column) {
        if (column.isAutoId()) {
            return null;
        }
        String key = column.getName();
        Object value = column.getFieldValue(entity);
        return new MMJKeyValue(key, value);
    }

    public static class MMJKeyValue {
        public final String key;
        public final Object value;

        public MMJKeyValue(String key, Object value) {
            this.key = key;
            this.value = value;
        }

        public String toString() {
            return "MMJKeyValue{" + "key='" + key + '\'' + ", value=" + value + '}';
        }
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            MMJKeyValue keyValue = (MMJKeyValue) o;
            return key == null ? keyValue.key == null : key.equals(keyValue.key);

        }
        public int hashCode() {
            return key != null ? key.hashCode() : 0;
        }
    }
}